package gz.itcast.c_prepared;

import gz.itcast.util.JdbcUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created 吴海杰 on 2017/2/27.
 */
public class Demo1 {
    @Test
    public void test1() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO student(name, gender) VALUES (?,?);";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "lisi");
            stmt.setString(2, "m");
            int count = stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }


    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "UPDATE student SET day0224.student.name=?  WHERE id=?;";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "lisi11");
            stmt.setInt(2, 3);
            int count = stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }


    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO student(name, gender) VALUES (?,?);";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "lisi");
            stmt.setString(2, "m");
            int count = stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }





    @Test
    public void testDelete() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "DELETE FROM student WHERE id=?;";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 3);

            int count = stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }


    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "SELECT *FROM student;";
            stmt = conn.prepareStatement(sql);


           ResultSet rs = stmt.executeQuery();
           while (rs.next()){
               int id=rs.getInt(1);
               String name=rs.getString(2);
               String gender =rs.getString(3);
               System.out.println(id+","+name+","+gender);
           }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.close(conn, stmt);
        }
    }
}
